package com.huatai.bi.service.impl;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.extra.pinyin.PinyinUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.analysis.ExcelReadExecutor;
import com.alibaba.excel.exception.ExcelAnalysisException;
import com.alibaba.excel.read.builder.ExcelReaderBuilder;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.baomidou.mybatisplus.core.toolkit.IdWorker;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.huatai.bi.constant.DataDirectorConstant;
import com.huatai.bi.constant.SystemConstant;
import com.huatai.bi.easyExcelListener.CustomInterceptorSaveData;
import com.huatai.bi.easyExcelListener.LimitExcelReadListener;
import com.huatai.bi.entity.DataSetColumnEntity;
import com.huatai.bi.entity.DirectDataSetEntity;
import com.huatai.bi.entity.DirectoryEntity;
import com.huatai.bi.service.DataSetColumnService;
import com.huatai.bi.service.DirectDataSetService;
import com.huatai.bi.service.DirectoryService;
import com.huatai.bi.vo.excelVo.ExcelDataVO;
import com.huatai.bi.vo.excelVo.HeardVO;
import com.huatai.bi.vo.excelVo.SubClassVO;
import com.huatai.common.error.exception.ServiceException;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.kafka.core.KafkaTemplate;
import org.springframework.stereotype.Service;
import org.springframework.util.ObjectUtils;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.nio.charset.Charset;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.CompletableFuture;
import java.util.concurrent.Executors;

/**
 * @BelongsProject: itc-airport-cloud
 * @BelongsPackage: com.itc.bi.service.impl
 * @Author: hecaiy
 * @CreateTime: 2024-09-17  16:44
 * @Description: TODO
 * @Version: 1.0
 */
@Service
public class ImportExcelEasyPoiService {
    @Autowired
    private DirectoryService directoryService;
    @Autowired
    private DirectDataSetService directDataSetService;
    @Autowired
    private ClickHouseJDBCService clickHouseJDBCService;
    @Autowired
    private DataSetColumnService dataSetColumnService;
    @Autowired
    private KafkaTemplate<String, Object> kafkaTemplate;


    /**
     * 上传文件，获取左侧树结构
     * @param file
     * @return
     * @throws IOException
     */
    public ExcelDataVO getExcelImportSheetTree(MultipartFile file) throws IOException {
        String originalFilename = file.getOriginalFilename();
        String rootName = originalFilename.substring(0, originalFilename.lastIndexOf("."));
        ExcelDataVO excelDataVO = new ExcelDataVO();
        InputStream inputStream = file.getInputStream();
        // 读取Excel文件
        if(originalFilename.endsWith(".csv")){
            List<SubClassVO> subClassVOS = new ArrayList<>();
            subClassVOS.add(new SubClassVO(rootName, new ArrayList<>(),new ArrayList<>()));
            excelDataVO.setRootDirectory(rootName);
            excelDataVO.setSubClassVOS(subClassVOS);
            return excelDataVO;
        }
        ExcelReaderBuilder readerBuilder = EasyExcel.read(inputStream);
        ExcelReader excelReader = readerBuilder.build();
        ExcelReadExecutor excelReadExecutor = excelReader.excelExecutor();
        List<ReadSheet> sheets = excelReadExecutor.sheetList();
        List<SubClassVO> subClassVOS = new ArrayList<>();
        for (ReadSheet readSheet : sheets){
            SubClassVO subClassVO = new SubClassVO();
            subClassVO.setSheetName(readSheet.getSheetName());
            subClassVOS.add(subClassVO);
        }
        excelDataVO.setRootDirectory(rootName);
        excelDataVO.setSubClassVOS(subClassVOS);
        return excelDataVO;
    }

    /**
     * 上传文件，根据sheet名称获取数据
     * @param file
     * @return
     * @throws IOException
     */
    public SubClassVO getExcelImportSheetData(MultipartFile file,String sheetName,Integer limitSize) throws IOException {
        SubClassVO excelDataVOS = new SubClassVO();
        excelDataVOS.setSheetName(sheetName);
        InputStream inputStream = file.getInputStream();
        String originalFilename = file.getOriginalFilename();
        //初始化一个监听器
        LimitExcelReadListener dataListener = new LimitExcelReadListener(limitSize);
        //读取文件数据
        try {
            if(originalFilename.endsWith(".csv")){
                EasyExcel.read(inputStream, dataListener).excelType(ExcelTypeEnum.CSV).charset(Charset.forName("GBK")).sheet().doRead();
            }else if(originalFilename.endsWith(".xls")) {
                EasyExcel.read(inputStream, dataListener).excelType(ExcelTypeEnum.XLS).sheet().sheetName(sheetName).doRead();
            }else {
                EasyExcel.read(inputStream, dataListener).sheet().sheetName(sheetName).doRead();
            }
            excelDataVOS.setBodyVos(dataListener.getBodyVos());
            excelDataVOS.setHeardVos(dataListener.getHeardVos());
        } catch (ExcelAnalysisException a) {
            excelDataVOS.setBodyVos(dataListener.getBodyVos());
            excelDataVOS.setHeardVos(dataListener.getHeardVos());
        }
        return excelDataVOS;
    }

    /**
     * 读取excel 创建数据集
     * @param file
     * @param sheet
     * @param dtId
     * @return
     * @throws IOException
     */
    public Boolean createFileDataSet(MultipartFile file,List<String> sheet,Long dtId) throws IOException {
        if(ObjectUtils.isEmpty(dtId)){
            throw new ServiceException("目录不能为空");
        }
        String originalFilename = file.getOriginalFilename();
        List<SubClassVO> subClassVOS = new ArrayList<>();
        List<DirectoryEntity> directoryEntityList = new ArrayList<>();
        List<DirectDataSetEntity> dataSetEntityList = new ArrayList<>();

        for (String sheetName : sheet){
            String tablenameNote = originalFilename.substring(0, originalFilename.lastIndexOf("."))+sheetName;
            //数据表名称用stic_拼音首字母+时间戳
            String tableName = "stic_"+ PinyinUtil.getFirstLetter(sheetName,"")+"_"+ IdWorker.getId();
            //TODO 此处还差文件类型验证

            //判断是否存在数据
            SubClassVO excelImportSheetData = this.getExcelImportSheetData(file, sheetName, 10);
            //非空创建数据集
            if(excelImportSheetData!=null && excelImportSheetData.getBodyVos()!=null && excelImportSheetData.getBodyVos().size()>0){
                //TODO 此处还差添加数据集目录逻辑
                DirectoryEntity directoryEntity = new DirectoryEntity();
                directoryEntity.setName(tablenameNote);
                long count = directoryService.count((Wrappers.<DirectoryEntity>lambdaQuery().eq(DirectoryEntity::getPid, dtId).likeRight(DirectoryEntity::getName, tablenameNote)));
                if(count>0){
                    long temp = count+1;
                    directoryEntity.setName(tablenameNote+temp);
                }
                directoryEntity.setIsDir( DataDirectorConstant.ISDATASET);
                directoryEntity.setPid(dtId);
                directoryEntity.setTableName(tableName);
                directoryEntity.setDtId(IdWorker.getId());
                directoryEntityList.add(directoryEntity);
                DirectDataSetEntity directDataSetEntity = new DirectDataSetEntity();
                directDataSetEntity.setId(directoryEntity.getDtId());
                directDataSetEntity.setDtId(dtId);
                directDataSetEntity.setTableName(tableName);
                directDataSetEntity.setType(SystemConstant.DATA_SET_TYPE_EXCEL);
                dataSetEntityList.add(directDataSetEntity);

                //操作数据集数据项
                List<HeardVO> heardVos = excelImportSheetData.getHeardVos();
                List<DataSetColumnEntity> list = new ArrayList<>();
                //构建数仓，数据同步，将数据库表字段信息入库
                for (int i = 0; i < heardVos.size(); i++) {
                    HeardVO heardVO = heardVos.get(i);
                    DataSetColumnEntity entity = new DataSetColumnEntity();
                    entity.setId(IdWorker.getId());
                    entity.setDataSetId(directoryEntity.getDtId());
                    entity.setColumnName(heardVO.getKey());
                    entity.setColumnComment(heardVO.getTitle());
                    entity.setColumnType(heardVO.getCharacterType());
                    entity.setColumnPosition(i+1);
                    list.add(entity);
                }
                dataSetColumnService.remove(Wrappers.<DataSetColumnEntity>lambdaQuery().eq(DataSetColumnEntity::getDataSetId,dtId));
                dataSetColumnService.saveBatch(list);
                clickHouseJDBCService.createTableByColumnList(tableName, list, "");
            }
            //创建数据集后 写入数据
            CompletableFuture.runAsync(()->{
                CustomInterceptorSaveData customInterceptorSaveData = new CustomInterceptorSaveData(tableName,clickHouseJDBCService,null,null,excelImportSheetData.getHeardVos());
                try {
                    if(originalFilename.endsWith(".csv")){
                        EasyExcel.read(file.getInputStream(), customInterceptorSaveData).excelType(ExcelTypeEnum.CSV).charset(Charset.forName("GBK")).sheet().doRead();
                    }else if(originalFilename.endsWith(".xls")) {
                        EasyExcel.read(file.getInputStream(), customInterceptorSaveData).excelType(ExcelTypeEnum.XLS).sheet().sheetName(sheetName).doRead();
                    }else {
                        EasyExcel.read(file.getInputStream(), customInterceptorSaveData).excelType(ExcelTypeEnum.XLSX).sheet().sheetName(sheetName).doRead();
                    }

                } catch (IOException e) {}
            }, Executors.newSingleThreadExecutor());
        }
        if(CollectionUtil.isNotEmpty(directoryEntityList) && CollectionUtil.isNotEmpty(dataSetEntityList)){
            directDataSetService.saveBatch(dataSetEntityList);
            directoryService.saveBatch(directoryEntityList);
            return true;
        }
        return false;
    }

    /**
     * 修改excel数据集数据
     * @param file  文件
     * @param sheet 文件对应的sheet
     * @return
     */
    public Boolean updateFileDataSet(MultipartFile file,String sheetName,Long dtId) throws IOException {
        if(ObjectUtils.isEmpty(dtId)){
            throw new ServiceException("数据集不能为空");
        }
        DirectoryEntity directoryEntity = directoryService.getById(dtId);
        if(ObjectUtil.isEmpty(directoryEntity)){
            throw new ServiceException("数据集不存在");
        }
        String originalFilename = file.getOriginalFilename();
        List<SubClassVO> subClassVOS = new ArrayList<>();
        //判断是否存在数据
        SubClassVO excelImportSheetData = this.getExcelImportSheetData(file, sheetName, 10);
        if (excelImportSheetData!=null && excelImportSheetData.getBodyVos()!=null && excelImportSheetData.getBodyVos().size()>0){
            List<DataSetColumnEntity> dataSetColumnEntities = dataSetColumnService.list(Wrappers.<DataSetColumnEntity>lambdaQuery().eq(DataSetColumnEntity::getDataSetId, dtId));
            //判断数据项是否完全相同
            SubClassVO subClassVO = subClassVOS.get(0);
            List<HeardVO> heardVos = subClassVO.getHeardVos();
            if(heardVos.size()!=dataSetColumnEntities.size()){
                throw new ServiceException("数据项不一致，数据导入失败");
            }
            for (int i = 0; i < heardVos.size(); i++) {
                HeardVO heardVO = heardVos.get(i);
                DataSetColumnEntity setColumnEntity = dataSetColumnEntities.stream().filter(p -> p.getColumnName().equalsIgnoreCase(heardVO.getKey())).findFirst().orElse(null);
                //判断字段是否一致
                if(ObjectUtil.isEmpty(setColumnEntity)){
                    throw new ServiceException("数据项不一致，数据导入失败");
                }
            }
            String tableNameOld = directoryEntity.getTableName();
            String tableNameNew = tableNameOld.substring(0, tableNameOld.lastIndexOf("_"));
            tableNameNew = tableNameNew+"_"+IdWorker.getId();
            //数据更新为全量更新
            clickHouseJDBCService.createTableByColumnList(tableNameNew, dataSetColumnEntities, "");
            //创建数据集后 写入数据
            String finalTableNameNew = tableNameNew;
            CompletableFuture.runAsync(()->{
                CustomInterceptorSaveData customInterceptorSaveData = new CustomInterceptorSaveData(finalTableNameNew,clickHouseJDBCService,dtId,kafkaTemplate,excelImportSheetData.getHeardVos());
                try {
                    if(originalFilename.endsWith(".csv")){
                        EasyExcel.read(file.getInputStream(), customInterceptorSaveData).excelType(ExcelTypeEnum.CSV).charset(Charset.forName("GBK")).sheet().doRead();
                    }else if(originalFilename.endsWith(".xls")) {
                        EasyExcel.read(file.getInputStream(), customInterceptorSaveData).excelType(ExcelTypeEnum.XLS).sheet().sheetName(sheetName).doRead();
                    }else {
                        EasyExcel.read(file.getInputStream(), customInterceptorSaveData).sheet().sheetName(sheetName).doRead();
                    }
                    //切换表
                    directoryEntity.setTableName(finalTableNameNew);
                    directoryService.updateById(directoryEntity);
                    //通知成功之后删除旧表
                    //clickHouseJDBCService.dropTable(tableNameOld);
                } catch (IOException e) {}
            }, Executors.newSingleThreadExecutor());
            return true;
        }
        return false;
    }
}
